package com.apes.auto.autoparts.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import com.apes.framework.rop.converter.DateUtils;
import com.apes.framework.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class AutopartsDao extends ApesDao {

    @Autowired
    SqlService sqlService;
    @Autowired
    private EntityManager entityManager;

    /***
     * 查询EPC_VIN_ID
     */
    public JSONArray checkVIN_EPC_ID(JSONObject param) {
        String sSQL = "SELECT A.EPC_BBMC, A.MY_MODEL_STD_ID, A.VIN, A.TS01, A.LY, A.LRR,\n" +
                "          A.YXBJ\n" +
                "     FROM SJZX.VIN_EPC_ID A\n" +
                "    WHERE A.VIN = :vin\n" +
                "      AND A.YXBJ = 1";
        return toJSONArray(this.find(sSQL, param));
    }

    /***
     * 查询CHX_EPC_ID
     */
    public JSONArray checkCHX_EPC_ID(JSONObject param) {
        String sSQL = "SELECT A.EPC_BBMC, A.MY_MODEL_STD_ID, A.PZCXID, A.QCCPMC,\n" +
                "             A.TS01, A.LY, A.LRR, A.YXBJ\n" +
                "        FROM SJZX.CHX_EPC_ID A\n" +
                "       WHERE A.PZCXID = :vehicleConfig\n" +
                "         AND A.QCCPMC = :brandName\n" +
                "         AND A.YXBJ = 1";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 插入EPC_VIN_ID
     */

    public void insertEPC_VIN_ID(JSONObject param, JSONObject insertData) {
        JSONObject tempJson = new JSONObject();
        tempJson.putAll(insertData);
        tempJson.put("VIN", param.getString("vin"));
        tempJson.put("LRR", "yyadmin");
        tempJson.put("LY", "车型对照");
        tempJson.put("TS01", DateUtils.format(new Date(), "yyyyMMdd.HHmmss.SSS"));
//        sqlService.insertTable("SJZX.EPC_VIN_ID", new com.apestech.framework.json.JSONObject(tempJson)); // TODO： 检查为何无法插入数据
    }

    /***
     * 查询当前epc车型和版本对应的标准品类信息
     */

    public JSONArray getStardardCatalog(JSONObject param) {
        String sSQL = "SELECT B.PLID\n" +
                "  FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                " WHERE A.MJBJ = 1\n" +
                "   AND A.EPC_BBMC = B.EPC_BBMC\n" +
                "   AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "   AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "   AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "   AND A.EPC_BBMC = :epcVersion\n" +
                "   AND A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND B.PLID IS NOT NULL\n" +
                " GROUP BY B.PLID";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 查询当前epc车型和版本对应的epc目录主组
     */
    public JSONArray getEpcMenu(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL, A.CATA_INDEX,\n" +
                "       A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL, A.IMGNAME,\n" +
                "       A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ, A.REMARK1, A.REMARK2\n" +
                "  FROM SJZX.EPC_STD_CATA A\n" +
                " WHERE A.CATA_LEVEL = 1\n" +
                "   AND A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND A.EPC_BBMC = :epcVersion ORDER BY A.CATA_INDEX";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 查询当前品类列表数据
     */

    public JSONArray queryCurrentPlxx(JSONArray array) {
        JSONArray ids = new JSONArray();
        for (int i = 0, len = array.size(); i < len; i++) {
            ids.add(array.getJSONObject(i).getString("PLID"));
        }
        String sSQL = "SELECT A.* FROM ( SELECT T.* FROM SJZXERP.MAS_PLXX T WHERE T.PLID IN (" + translateArrayToString(ids, ",") + ") ) A ORDER BY A.PLID";
        return toJSONArray(this.find(sSQL));
    }


    /***
     * 查询上一级的品类并去重
     */
    public JSONArray queryParentUnique(JSONArray array) {
        JSONArray ids = new JSONArray();
        for (int i = 0, len = array.size(); i < len; i++) {
            ids.add(array.getJSONObject(i).getString("PLID"));
        }
        String sSQL = "SELECT A.* FROM ( SELECT B.* FROM ( SELECT T.SJID FROM MAS_PLXX T WHERE T.PLID IN (" + translateArrayToString(ids, ",") + ") " +
                " GROUP BY T.SJID) A, MAS_PLXX B WHERE A.SJID = B.PLID) A ORDER BY A.PLID";

        return toJSONArray(this.find(sSQL));
    }


    /***
     * 关联查询导入搜索引擎
     * @param param
     * @return
     */
    public JSONArray queryEpcMenuFromOracle(JSONObject param) {
        param.put("TS01", "20190722.120000.000");
        String sSQL = "SELECT * FROM (SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL, A.CATA_INDEX,\n" +
                "       A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL, A.IMGNAME,\n" +
                "       A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ, A.REMARK1, A.REMARK2,\n" +
                "       B.PLID\n" +
                "  FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                " WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "   AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "   AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "   AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "   AND A.TS01 > :TS01 ORDER BY A.TS01) WHERE ROWNUM <= 1000";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 将String数组转化为String
     * @param array
     * @param regex
     * @return
     */
    private String translateArrayToString(JSONArray array, String regex) {
        String returnStr = "";
        for (int i = 0; i < array.size(); i++) {
            if (i != array.size() - 1) {
                returnStr = returnStr + "'" + array.getString(i) + "'" + regex;
            } else {
                returnStr = returnStr + "'" + array.getString(i) + "'";
            }
        }
        return returnStr;
    }


    /***
     * epc 获取下一级
     */

    public JSONArray getSubgroupForEpc(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL,\n" +
                "          A.CATA_INDEX, A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL,\n" +
                "          A.IMGNAME, A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ,\n" +
                "          A.REMARK1, A.REMARK2\n" +
                "     FROM SJZX.EPC_STD_CATA A\n" +
                "     WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.EPC_BBMC = :epcVersion\n" +
                "     AND A.PREV_ID = :id ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray getSubgroupForOwner(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL,\n" +
                "          A.CATA_INDEX, A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL,\n" +
                "          A.IMGNAME, A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ,\n" +
                "          A.REMARK1, A.REMARK2\n" +
                "     FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                "     WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "     AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "     AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "     AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "     AND A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.EPC_BBMC = :epcVersion\n" +
                "     AND A.MJBJ = 1\n" +
                "     AND B.PLID = :id" +
                "     ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 获取配件信息（关联配件表和编译表）
     */
    public JSONArray getAnalysisParts(JSONObject param, String tableName) {
        String sSQL = "SELECT A.PARTS_ID, A.MY_MODEL_STD_ID, A.CURRENT_ID, A.PNC, A.PARTNUMBER,\n" +
                "         A.PARTNAME, A.PARTNAME_ZH, A.QTY, A.REMARK1, A.REMARK2,\n" +
                "         A.REPLACEMENT, A.COST_4S, A.PRICE_4S, A.PRICEUPDATEDAT, A.LY,\n" +
                "         B.EPC_BBMC, B.PJMCID, B.PJMC, B.OEMS\n" +
                "    FROM SJZX." + tableName + " A, EPC_OE_PJMC B\n" +
                "   WHERE A.PARTNUMBER = B.PARTNUMBER(+)\n" +
                "     AND A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND B.EPC_BBMC = :epcVersion\n" +
                "     AND A.CURRENT_ID = :id ORDER BY A.PARTS_ID";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 查询配件关联的图片信息
     */
    public JSONArray getImageUrl(JSONObject param) {
        String sSQL = "SELECT A.PARTNUMBER, A.IMGNAME, A.LRR, A.TS01\n" +
                "    FROM SJZX.EPC_PICTURE A\n" +
                "   WHERE A.PARTNUMBER = :PARTNUMBER";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 获取商品
     */

    public JSONArray getProduct(JSONObject param) {
        String sSQL = "SELECT A.SPID, A.SPMC, A.PLID, A.PLMC, A.PPID, A.PPMC, A.CJID, A.CJMC,"
                + "       A.SPZID, A.SPZMC, A.SPZT, A.SPXH, A.GG, A.BZSM, A.CD, A.EANID, "
                + "       A.OEMID, A.IAMID, A.YS, A.JEHSBJ, A.SLHSBJ, A.VIPYELX, A.ZDYBJ, "
                + "       A.FWSPBJ, A.XZWZBJ, A.CXZKBJ, A.TS01, A.LRR, A.LRRQ_SJ, A.CGZID,"
                + "       B.JLDW, B.HSBL, A.SSLBID, A.SSLBMC, A.SB, A.SSBL, A.CGZMC, A.JSPID,"
                + "       A.DSFSPID, A.FFBJ, A.DMCJ, A.BZQX, A.BXQX, A.SGFS, A.SPZCLX,"
                + "       A.PJMCID, A.PJMC, A.PYM, A.FWLXID, A.FWLXMC,A.KZZCBJ,A.CPMS, A.KSFWBJ, A.SPID ERPSPID,"
                + "		  B.ZCMRBJ, B.CGMRBJ, B.XSMRBJ, B.HYDWBJ"
                + "  FROM MAS_SPXX A, MAS_SPBZ B"
                + " WHERE A.SPID=B.SPID AND B.HSBL=1 AND A.SPZT <> 3 "
                + "   AND A.PJMCID = #{PJMCID} AND ';'||A.OEMID||';' LIKE '%;'|| #{PARTNUMBER} ||';%' ";
        return toJSONArray(find(sSQL, param));
    }


    /**
     * 获取EPC_配件目录汇总
     *
     * @param param
     * @return
     */
    public List<Map> queryEPC_STD_CATA(JSONObject param) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT T.CATA_ID,T.CURRENT_ID,T.EPC_BBMC,T.MY_MODEL_STD_ID,T.NAME_ZH,T.CATA_INDEX,T.CATA_LEVEL,T.PREV_ID,T.NAME,T.NAME_ZH_ORIGINAL," +
                "T.IMGNAME,T.MJBJ,T.TS01,T.LY,T.KSRQ,T.JSRQ,T.REMARK1,T.REMARK2,T.YXBJ\n " +
                "FROM EPC_STD_CATA T\n" +
                " WHERE 1=1  AND T.YXBJ=1\n");

        if (param.containsKey("CATA_ID")) {
            sql.append(" AND T.CATA_ID=:CATA_ID\n");
        }
        if (param.containsKey("MY_MODEL_STD_ID")) {
            sql.append(" AND T.MY_MODEL_STD_ID=:MY_MODEL_STD_ID\n");
        }
        if (param.containsKey("EPC_BBMC")) {
            sql.append(" AND T.EPC_BBMC=:EPC_BBMC\n");
        }
        return this.find(sql.toString(), param);
    }

    public int queryMaxCurrentId(JSONObject param) {
        String sql = "SELECT MAX(to_number(t.CURRENT_ID)) AS CURRENT_ID  FROM EPC_STD_CATA T WHERE T.EPC_BBMC =:EPC_BBMC  AND MY_MODEL_STD_ID =:MY_MODEL_STD_ID";
        return Integer.valueOf(this.find(sql, param).get(0).get("CURRENT_ID").toString());

    }

    /**
     * 获取EPC配件目录VS标准化分类
     *
     * @param param
     * @return
     */
    public JSONArray queryEPC_STD_CATA_DZB(JSONObject param) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT T.EPC_BBMC,T.CATA_LEVEL,T.CATA_INDEX,T.CURRENT_ID,T.PLID,T.TS01 FROM\n" +
                " EPC_STD_CATA_DZB T\n" +
                " WHERE 1=1 \n");

        if (param.containsKey("CURRENT_ID")) {
            sql.append(" AND T.CURRENT_ID=:CURRENT_ID\n");
        }
        if (param.containsKey("EPC_BBMC")) {
            sql.append(" AND T.EPC_BBMC=:EPC_BBMC\n");
        }
        if (param.containsKey("CATA_INDEX")) {
            sql.append(" AND T.CATA_INDEX=:CATA_INDEX\n");
        }
        if (param.containsKey("CATA_LEVEL")) {
            sql.append(" AND T.CATA_LEVEL=:CATA_LEVEL\n");
        }
        return toJSONArray(this.find(sql.toString(), param));
    }

    /**
     * 新增EPC_配件目录汇总
     *
     * @param param
     */
    public void insertEPC_STD_CATA(JSONObject param) {
        param.put("TS01", DateUtil.format(new Date(), "yyyyMMdd.hhmmss.SSS"));
        param.put("YXBJ", 1);
        param.put("CATA_ID", param.get("EPC_BBMC") + "_" + param.get("MY_MODEL_STD_ID") + "_" + param.get("CATA_LEVEL") + "_" + param.get("CURRENT_ID"));
        sqlService.insertTable("EPC_STD_CATA", new com.apestech.framework.json.JSONObject(param));

    }

    /**
     * 修改EPC_配件目录汇总
     *
     * @param param
     */
    public void updateEPC_STD_CATA(JSONObject param) {
        String sql = "update EPC_STD_CATA set NAME=?,NAME_ZH=?,NAME_ZH_ORIGINAL=?," +
                "IMGNAME=?,MJBJ=?,TS01=?,LY=?,KSRQ=?,JSRQ=?,REMARK1=?,REMARK2=? where CATA_ID=?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, param.get("NAME"));
        query.setParameter(2, param.get("NAME_ZH"));
        query.setParameter(3, param.get("NAME_ZH_ORIGINAL"));
        query.setParameter(4, param.get("IMGNAME"));
        query.setParameter(5, param.get("MJBJ"));
        query.setParameter(6, DateUtil.format(new Date(), "yyyyMMdd.hhmmss.SSS"));
        query.setParameter(7, param.get("LY"));
        query.setParameter(8, param.get("KSRQ"));
        query.setParameter(9, param.get("JSRQ"));
        query.setParameter(10, param.get("REMARK1"));
        query.setParameter(11, param.get("REMARK2"));
        query.setParameter(12, param.get("CATA_ID"));
        query.executeUpdate();
        //sqlService.update("EPC_STD_CATA", new com.apestech.framework.json.JSONObject(param)); // TODO 用不了

    }

    /**
     * 删除EPC_配件目录汇总
     *
     * @param param
     */
    public void deleteEPC_STD_CATA(JSONObject param) {
        String sql = "update EPC_STD_CATA set YXBJ=0 where CATA_ID=?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, param.get("CATA_ID"));
        query.executeUpdate();
    }

    /**
     * 新增EPC配件目录VS标准化分类
     *
     * @param param
     */
    public void insertEPC_STD_CATA_DZB(JSONObject param) {
        param.put("TS01", DateUtil.format(new Date(), "yyyyMMdd.hhmmss.SSS"));
        sqlService.insertTable("EPC_STD_CATA_DZB", new com.apestech.framework.json.JSONObject(param));
    }

    public void updateEPC_STD_CATA_DZB(JSONObject param) {
        /**
         * 没有主键 没有唯一条件  一对多 情况下无法执行更新操作
         String sql = "update EPC_STD_CATA_DZB set PLID=?,TS01=? where EPC_BBMC=? and CATA_LEVEL=? and CATA_INDEX=? and CURRENT_ID=?";
         Query query = entityManager.createNativeQuery(sql);
         query.setParameter(1, param.get("PLID"));
         query.setParameter(2, DateUtil.format(new Date(), "yyyyMMdd.hhmmss.SSS"));
         query.setParameter(3, param.get("EPC_BBMC"));
         query.setParameter(4, param.get("CATA_LEVEL"));
         query.setParameter(5, param.get("CATA_INDEX"));
         query.setParameter(6, param.get("CURRENT_ID"));
         query.executeUpdate();
         **/

        // sqlService.update("EPC_STD_CATA_DZB", new com.apestech.framework.json.JSONObject(param));// TODO 用不了
    }

    /**
     * 删除EPC配件目录VS标准化分类
     *
     * @param param
     */
    public void deleteEPC_STD_CATA_DZB(JSONObject param) {
        String sql = "delete from EPC_STD_CATA_DZB where EPC_BBMC=? and CATA_LEVEL=? and CATA_INDEX=? and CURRENT_ID=?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, param.get("EPC_BBMC"));
        query.setParameter(2, param.get("CATA_LEVEL"));
        query.setParameter(3, param.containsKey("CATA_INDEX") ? param.get("CATA_INDEX") : null);
        query.setParameter(4, param.get("CURRENT_ID"));
        query.executeUpdate();
    }


    /**
     * 新增EPC版本
     *
     * @param param
     */
    public void insertEPC_OE_V(JSONObject param) {
        sqlService.insertTable("EPC_" + param.getString("EPC_BBMC"), new com.apestech.framework.json.JSONObject(param));

    }

    /**
     * 查询EPC版本
     *
     * @param param
     * @return
     */
    public List<Map> searchEPC_OE_V(JSONObject param) {
        String sSQL = "SELECT T.PARTS_ID,T.MY_MODEL_STD_ID,T.CURRENT_ID,T.PNC,T.PARTNUMBER,T.PARTNAME,T.PARTNAME_ZH,T.QTY," +
                "T.REMARK1,T.REMARK2,T.REPLACEMENT,T.COST_4S,T.PRICE_4S,T.PRICEUPDATEDAT,T.LY" +
                "\n FROM EPC_" + param.get("EPC_BBMC") + "\n T " +
                " WHERE T.MY_MODEL_STD_ID=:MY_MODEL_STD_ID AND T.PARTNUMBER=:PARTNUMBER";
        return this.find(sSQL, param);
    }

    /**
     * 新增各版本EPC的OE编译结果
     *
     * @param param
     */
    public void insertEPC_OE_PJMC(JSONObject param) {
        sqlService.insertTable("EPC_OE_PJMC", new com.apestech.framework.json.JSONObject(param));
    }

    /**
     * 查询各版本EPC的OE编译结果
     *
     * @param param
     */
    public JSONArray searchEPC_OE_PJMC(JSONObject param) {
        String sSQL = "SELECT T.EPC_BBMC,T.PARTNUMBER,T.PJMCID ,T.PJMC,T.OEMS,T.LRR,T.TS01 FROM EPC_OE_PJMC T WHERE T.EPC_BBMC=:EPC_BBMC AND T.PARTNUMBER=:PARTNUMBER ";
        return toJSONArray(this.find(sSQL, param));
    }

    public void updateEPC_V(JSONObject param) {
        String sql = "UPDATE  EPC_" + param.get("EPC_BBMC") + " SET PNC=?,PARTNAME=?,PARTNAME_ZH=?,QTY=?," +
                "REMARK1=?,REMARK2=?,REPLACEMENT=?,COST_4S=?,PRICE_4S=?,PRICEUPDATEDAT=?,LY=? " +
                "WHERE MY_MODEL_STD_ID=?  AND CURRENT_ID=? AND PARTNUMBER=?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, param.get("PNC"));
        query.setParameter(2, param.get("PARTNAME"));
        query.setParameter(3, param.get("PARTNAME_ZH"));
        query.setParameter(4, param.get("QTY"));
        query.setParameter(5, param.get("REMARK1"));
        query.setParameter(6, param.get("REMARK2"));
        query.setParameter(7, param.get("REPLACEMENT"));
        query.setParameter(8, param.get("COST_4S"));
        query.setParameter(9, param.get("PRICE_4S"));
        query.setParameter(10, param.get("PRICEUPDATEDAT"));
        query.setParameter(11, param.get("LY"));
        query.setParameter(12, param.get("MY_MODEL_STD_ID"));
        query.setParameter(13, param.get("CURRENT_ID"));
        query.setParameter(14, param.get("PARTNUMBER"));
        query.executeUpdate();
    }

    public void deleteEPC_OE_PJMC(JSONObject param) {
        String sql = "DELETE FROM EPC_OE_PJMC WHERE EPC_BBMC=? AND PARTNUMBER=?";

        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, param.get("EPC_BBMC"));
        query.setParameter(2, param.get("PARTNUMBER"));
        query.executeUpdate();
    }

    public void deleteMAS_ZPT_PBFW() {
        String sSQL = "DELETE FROM MAS_ZPT_PBFW";
        delete(sSQL, new JSONObject());
    }

    public int addMAS_ZPT_PBFW(JSONObject param) {
        String sSQL = "INSERT INTO MAS_ZPT_PBFW\n" +
                "  (QCCPMC, QCCPID)\n" +
                "VALUES\n" +
                "  ()";
        return insert(sSQL, param);
    }
}
